<html>
  <head>
    <title>postgres__define.pro</title>
    <style type="text/css">
    <!--
    body {
      color: #000000;
      background-color: #ffffff;
    }
    .comment {
      /* font-lock-comment-face */
      color: #b22222;
    }
    .constant {
      /* font-lock-constant-face */
      color: #5f9ea0;
    }
    .function-name {
      /* font-lock-function-name-face */
      color: #0000ff;
    }
    .keyword {
      /* font-lock-keyword-face */
      color: #a020f0;
    }
    .string {
      /* font-lock-string-face */
      color: #bc8f8f;
    }
    .variable-name {
      /* font-lock-variable-name-face */
      color: #b8860b;
    }

    a {
      color: inherit;
      background-color: inherit;
      font: inherit;
      text-decoration: inherit;
    }
    a:hover {
      text-decoration: underline;
    }
    -->
    </style>
  </head>
<pre>

<span class="comment">;+</span>
<span class="comment">; NAME:</span>
<span class="comment">;  POSTGRES   (IDL Class file)</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  An IDL class file wrapping the pgsql_query() function, which provides</span>
<span class="comment">;  an interface to the postgres database.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  pg = obj_new('postgres', connect_info=)</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL INPUTS:</span>
<span class="comment">;  connect_info: This is stored at initialization so can be used later</span>
<span class="comment">;    without sending. Useful if not using the database listed in the</span>
<span class="comment">;    PGDATABASE environment variable for example.</span>
<span class="comment">;</span>
<span class="comment">; METHODS:</span>
<span class="comment">;  All the functionality comes from the pgsql_query() function.  This</span>
<span class="comment">;  class provides some wrapper methods for complex but often-used queries.</span>
<span class="comment">;  Use:</span>
<span class="comment">;          methods,'postgres' </span>
<span class="comment">;</span>
<span class="comment">;  to list the methods and </span>
<span class="comment">;          doc_method,'postgres::methodname' </span>
<span class="comment">;</span>
<span class="comment">;  to see the full documentation for each. Use </span>
<span class="comment">;          doc_method,'postgres',/class  (or doc_library,'postgres__define')</span>
<span class="comment">;  to see this doc.</span>
<span class="comment">; </span>
<span class="comment">;</span>
<span class="comment">;  The most useful methods:</span>
<span class="comment">;    ::query()</span>
<span class="comment">;       Send a query and return the results.</span>
<span class="comment">;</span>
<span class="comment">;    ::struct2table: Stuff a structure into a postgres table, creating a</span>
<span class="comment">;       new table if necessary.</span>
<span class="comment">;</span>
<span class="comment">;    ::tables()</span>
<span class="comment">;       Return a list of tables in the database.</span>
<span class="comment">;    ::table_exists() </span>
<span class="comment">;       Return 1 if the table exists, 0 if not.</span>
<span class="comment">;    ::describe</span>
<span class="comment">;       Print a description of a table or, if no arguments, short descriptions </span>
<span class="comment">;       of all tables.</span>
<span class="comment">;    ::table_indexes </span>
<span class="comment">;       Print index information for a table.</span>
<span class="comment">;</span>
<span class="comment">;    ::status_val()</span>
<span class="comment">;       Return the query status value given the name</span>
<span class="comment">;    ::status_name()</span>
<span class="comment">;       Return status name given the value</span>
<span class="comment">;</span>
<span class="comment">;    ::tablerows()</span>
<span class="comment">;       Postgres does not store the number of rows for a table, it counts them</span>
<span class="comment">;       each time you run select count(*) from table....  If this info has been</span>
<span class="comment">;       stored in a table called tablename_meta it is retrieved, otherwise an</span>
<span class="comment">;       error is given and execution is stopped.</span>
<span class="comment">;</span>
<span class="comment">; RESTRICTIONS:</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;   Created: Mid-2005, Erin Sheldon, Uchicago</span>
<span class="comment">;-</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">;  Copyright (C) 2005  Erin Sheldon, NYU.  erin dot sheldon at gmail dot com</span>
<span class="comment">;</span>
<span class="comment">;    This program is free software; you can redistribute it and/or modify</span>
<span class="comment">;    it under the terms of the GNU General Public License as published by</span>
<span class="comment">;    the Free Software Foundation; either version 2 of the License, or</span>
<span class="comment">;    (at your option) any later version.</span>
<span class="comment">;</span>
<span class="comment">;    This program is distributed in the hope that it will be useful,</span>
<span class="comment">;    but WITHOUT ANY WARRANTY; without even the implied warranty of</span>
<span class="comment">;    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the</span>
<span class="comment">;    GNU General Public License for more details.</span>
<span class="comment">;</span>
<span class="comment">;    You should have received a copy of the GNU General Public License</span>
<span class="comment">;    along with this program; if not, write to the Free Software</span>
<span class="comment">;    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA</span>
<span class="comment">;</span>
<span class="comment">;</span>

<span class="keyword">function</span><span class="function-name"> postgres::init</span>, connect_info=connect_info

  funcnames  = routine_info(/system,/functions)

  w = where(funcNames <span class="keyword">eq</span> <span class="string">'PGSQL_QUERY'</span>,nw)
  <span class="keyword">if</span> nw <span class="keyword">eq</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span>
      message,<span class="string">'The postgres library PGSQL_QUERY() is not available'</span>,/inf
      message,<span class="string">'See the SDSSIDL README file for help on compilation'</span>,/inf
      <span class="keyword">return</span>,0
  <span class="keyword">endif</span>


  self-&gt;set_parameters, $
    connect_info=connect_info, query_status=-1, nrows=0
  <span class="keyword">return</span>,1
<span class="keyword">end</span> 

<span class="keyword">pro</span><span class="function-name"> postgres::set_parameters</span>, connect_info=connect_info, query_status=query_status, nrows=nrows

  <span class="keyword">if</span> n_elements(connect_info) <span class="keyword">ne</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      self.connect_info = connect_info 
  <span class="keyword">endif</span> 
  <span class="keyword">if</span> n_elements(query_status) <span class="keyword">ne</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      self.query_status = query_status
  <span class="keyword">endif</span> 
  <span class="keyword">if</span> n_elements(nrows) <span class="keyword">ne</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      self.nrows = nrows
  <span class="keyword">endif</span> 

<span class="keyword">end</span> 

<span class="comment">;docstart::postgres::query</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::query()</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  Simple wrapper for pgsql_query().  Useful if the user has inherited the</span>
<span class="comment">;  class and wants to store certain things internally, such as status,</span>
<span class="comment">;  nrows. Also, can store connect_info at initialization of this class so you</span>
<span class="comment">;  don't have to send it each time.  This is useful if not connecting to</span>
<span class="comment">;  alternative databases from PGDATABASE, or as another user.  </span>
<span class="comment">;</span>
<span class="comment">;  Finally, better to use this than the pgsql_query() function directly in </span>
<span class="comment">;  your code because of a bug in IDL that it gives a compile error if that</span>
<span class="comment">;  C function has not been linked, which can be confusing.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;</span>
<span class="comment">;  res = pg-&gt;query(query, nrows=, connect_info=, file=, /append, /nointerrupt,</span>
<span class="comment">;                  /verbose, status=)</span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  The query in string form.</span>
<span class="comment">; </span>
<span class="comment">; OPTIONAL INPUTS:</span>
<span class="comment">;  connect_info:  List of connection options separated by semicolon.</span>
<span class="comment">;        e.g. "user=somename;dbname=somename"</span>
<span class="comment">;       http://www.postgresql.org/docs/8.0/interactive/libpq.html#LIBPQ-CONNECT</span>
<span class="comment">;  file=: File into which the result will be written.</span>
<span class="comment">;  /append: Append the file.</span>
<span class="comment">;  /nointerrupt: Normally pgsql_query() runs a busy loop waiting for results.</span>
<span class="comment">;      This loop checks if control-c has been sent and if so, stops the</span>
<span class="comment">;      query and returns no data.  If /nointerrupt is sent then no such busy</span>
<span class="comment">;      loop is run.  This saves CPU if, for example, little results are being</span>
<span class="comment">;      returned from a long query.  Also good for batch mode when an interrupt</span>
<span class="comment">;      cannot be sent anyway.  The query cannot be killed however without</span>
<span class="comment">;      help from the administrator.</span>
<span class="comment">;  /verbose: pgsql_query() will print some informationl messges.</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL OUTPUTS:</span>
<span class="comment">;  status: The status of the query.  See the ::status_val() method for</span>
<span class="comment">;    the meaning of this output.</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: Mid-2005  Erin Sheldon Uchicago</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::query</span>

<span class="keyword">function</span><span class="function-name"> postgres::query</span>, query, nrows=nrows, connect_info=connect_info, file=file, append=append, nointerrupt=nointerrupt, verbose=verbose, status=status
  
  self-&gt;reset

  nq = n_elements(query)
  <span class="keyword">if</span> nq <span class="keyword">eq</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      print,<span class="string">'-Syntax: res=pg-&gt;query(query_string, nrows=, connect_info=, file=, /append, /nointerrupt, /verbose, status=)'</span>
      print
      message,<span class="string">'halting'</span>
  <span class="keyword">endif</span> 

  <span class="keyword">if</span> n_elements(connect_info) <span class="keyword">eq</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      connect_info = self.connect_info
  <span class="keyword">endif</span> 

  <span class="comment">;; Now send the query. IDL has a bug that it gives a compile</span>
  <span class="comment">;; error if the pgsql_query() function was not linked (that</span>
  <span class="comment">;; doesn't happen for procedures).  So we use the CALL_FUNCTION</span>
  <span class="comment">;; procedure to avoid this bug.</span>

  struct = CALL_FUNCTION(<span class="string">'pgsql_query'</span>, $
                         query, $
                         nrows=nrows, $
                         connect_info=connect_info, $
                         file=file, append=append, $
                         nointerrupt=nointerrupt, $
                         verbose=verbose, status=status)
  
  self-&gt;set_parameters, query_status=status, nrows=nrows

  <span class="keyword">return</span>,struct
<span class="keyword">end</span> 

<span class="keyword">pro</span><span class="function-name"> postgres::query</span>, query, result=result, nrows=nrows, connect_info=connect_info, file=file, append=append, nointerrupt=nointerrupt, verbose=verbose, status=status

  on_error, 2
  nq = n_elements(query)
  <span class="keyword">if</span> nq <span class="keyword">eq</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      print,<span class="string">'-Syntax: pg-&gt;query, query_string, result=, nrows=, connect_info=, file=, /append, /nointerrupt, /verbose, status='</span>
      print
      print,<span class="string">'This is the procedural version of the query() method'</span>
      print,<span class="string">'  useful if no results are returned.  If you expect results,'</span>
      print,<span class="string">'  you should use the functional form res=pg-&gt;query() for '</span>
      print,<span class="string">'  readability'</span>
      print
      message,<span class="string">'Halting'</span>
  <span class="keyword">endif</span> 

  result = self-&gt;query(query, $
                       nrows=nrows, $
                       connect_info=connect_info, $
                       file=file, $
                       append=append, $
                       nointerrupt=nointerrupt, $
                       verbose=verbose, status=status)
<span class="keyword">end</span> 

<span class="comment">;docstart::postgres::status_name</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::status_name()</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  Return the status name associated with the value.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  print, pg-&gt;status_name(status)</span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  The status returned from pgsql_query()</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: Mid-2005  Erin Sheldon Uchicago</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::status_name</span>

<span class="keyword">function</span><span class="function-name"> postgres::status_name</span>, status_val
  <span class="keyword">case</span> status_val <span class="keyword">of</span>
      0: <span class="keyword">return</span>,<span class="string">'success'</span>
      1: <span class="keyword">return</span>,<span class="string">'connect_failure'</span>
      2: <span class="keyword">return</span>,<span class="string">'no_result'</span>
      3: <span class="keyword">return</span>,<span class="string">'write_failure'</span>
      4: <span class="keyword">return</span>,<span class="string">'fatal_error'</span>
      <span class="keyword">else:</span> message,<span class="string">'unknown status value: '</span>+ntostr(status_val)
  <span class="keyword">endcase</span> 
<span class="keyword">end</span> 

<span class="comment">;docstart::postgres::status_val</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::status_val()</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  Return the status val associated with the name.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  if status ne pg-&gt;status_val(status_name) then .....</span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  The name of the status. One of:</span>
<span class="comment">;    success, connect_failure, no_result, write_failure, fatal_error</span>
<span class="comment">;  </span>
<span class="comment">; OUTPUTS:</span>
<span class="comment">;      'success': <span class="keyword">return</span>,0</span>
<span class="comment">;      'connect_failure': <span class="keyword">return</span>,1</span>
<span class="comment">;      'no_result': <span class="keyword">return</span>,2</span>
<span class="comment">;      'write_failure':return,3</span>
<span class="comment">;      'fatal_error': <span class="keyword">return</span>,4</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: Mid-2005  Erin Sheldon Uchicago</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::status_val</span>

<span class="keyword">function</span><span class="function-name"> postgres::status_val</span>, status_name

  <span class="keyword">case</span> strlowcase(status_name) <span class="keyword">of</span>
      <span class="string">'success'</span>: <span class="keyword">return</span>,0
      <span class="string">'connect_failure'</span>: <span class="keyword">return</span>,1
      <span class="string">'no_result'</span>: <span class="keyword">return</span>,2
      <span class="string">'write_failure'</span>:return,3
      <span class="string">'fatal_error'</span>: <span class="keyword">return</span>,4
      <span class="keyword">else:</span> message,<span class="string">'unknown status name: '</span>,status_name
  <span class="keyword">endcase</span> 

<span class="keyword">end</span> 

<span class="keyword">function</span><span class="function-name"> postgres::connect_info</span>
  <span class="keyword">return</span>,self.connect_info
<span class="keyword">end</span> 

<span class="keyword">function</span><span class="function-name"> postgres::query_status</span>
  <span class="keyword">return</span>,self.query_status
<span class="keyword">end</span> 

<span class="keyword">function</span><span class="function-name"> postgres::nrows</span>
  <span class="keyword">return</span>,self.nrows
<span class="keyword">end</span> 

<span class="comment">;docstart::postgres::tables</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::tables(/all)</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  Return a listing of the tables in the current database, or that sent</span>
<span class="comment">;  by connect_info.  By default returns the "public" tables.  If /all is </span>
<span class="comment">;  sent then all are returned.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  tablenames = pg-&gt;tables(connect_info=, /all, /struct, status=)</span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  tablename:  The name of the table.</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL INPUTS:</span>
<span class="comment">;  /all: Return a list of all tables, not just public.</span>
<span class="comment">;  /struct: Instead of returning only the names, return a structure with</span>
<span class="comment">;    lots of info.</span>
<span class="comment">;  connect_info:  List of connection options separated by semicolon.</span>
<span class="comment">;        e.g. "user=somename;dbname=somename"</span>
<span class="comment">;   http://www.postgresql.org/docs/8.0/interactive/libpq.html#LIBPQ-CONNECT</span>
<span class="comment">;</span>
<span class="comment">; OUTPUTS:</span>
<span class="comment">;  The listing of tables in string array.</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL OUTPUTS:</span>
<span class="comment">;  status: The status of the query.  See the ::status_val() method for</span>
<span class="comment">;    the meaning of this output.</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: 2006-05-19  Erin Sheldon NYU</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::tables</span>

<span class="keyword">function</span><span class="function-name"> postgres::tables</span>, struct=struct, connect_info=connect_info, all=all, status=status

  query = <span class="string">"select * from pg_tables"</span>
  <span class="keyword">if</span> <span class="keyword">not</span> keyword_set(all) <span class="keyword">then</span> <span class="keyword">begin</span> 
      query = query + <span class="string">" where schemaname = 'public'"</span>
  <span class="keyword">endif</span> 

  res = self-&gt;query(query, connect_info=connect_info, status=status)
  <span class="keyword">if</span> status <span class="keyword">eq</span> self-&gt;status_val(<span class="string">'success'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
      <span class="keyword">if</span> keyword_set(struct) <span class="keyword">then</span> <span class="keyword">begin</span> 
          <span class="keyword">return</span>, res
      <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span> 
          <span class="keyword">return</span>, res.tablename
      <span class="keyword">endelse</span> 
  <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">if</span> status <span class="keyword">eq</span> self-&gt;status_val(<span class="string">'no_result'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
      <span class="keyword">if</span> <span class="keyword">not</span> keyword_set(all) <span class="keyword">then</span> <span class="keyword">begin</span> 
          message,<span class="string">'No public tables'</span>,/inf
      <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span> 
          message,<span class="string">'No tables returned'</span>,/inf
      <span class="keyword">endelse</span> 
      <span class="keyword">return</span>, -1
  <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span> 
      message,<span class="string">'Failed to query database for table names'</span>,/inf
      <span class="keyword">return</span>, -1
  <span class="keyword">endelse</span> 
<span class="keyword">end</span> 




<span class="comment">;docstart::postgres::table_exists</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::table_exists()</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  Check if the table exists in the current database, or</span>
<span class="comment">;    the one specified by connect_info</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  if pg-&gt;table_exists(tablename, connect_info=, status=st) then .....</span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  tablename:  The name of the table.</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL INPUTS:</span>
<span class="comment">;  connect_info:  List of connection options separated by semicolon.</span>
<span class="comment">;        e.g. "user=somename;dbname=somename"</span>
<span class="comment">;   http://www.postgresql.org/docs/8.0/interactive/libpq.html#LIBPQ-CONNECT</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL OUTPUTS:</span>
<span class="comment">;  status: The status of the query.  See the ::status_val() method for</span>
<span class="comment">;    the meaning of this output.</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: 2006-05-19  Erin Sheldon NYU</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::table_exists</span>


<span class="keyword">function</span><span class="function-name"> postgres::table_exists</span>, tablename, connect_info=connect_info, status=status

  status = 1
  ntab = n_elements(tablename)
  <span class="keyword">if</span> ntab <span class="keyword">eq</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      print,<span class="string">'-Syntax: if pg-&gt;table_exists(tablename,connect_info=, status=) then ..'</span>
      print
      message,<span class="string">'Halting'</span>
  <span class="keyword">endif</span> 

  tablenames = self-&gt;tables(connect_info=connect_info, status=status)
  <span class="keyword">if</span> status <span class="keyword">ne</span> self-&gt;status_val(<span class="string">'success'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
      <span class="keyword">return</span>, 0
  <span class="keyword">endif</span>  

  match, strlowcase(tablenames), strlowcase(tablename), mall, min
  <span class="keyword">if</span> mall[0] <span class="keyword">eq</span> -1 <span class="keyword">then</span> <span class="keyword">return</span>,0 <span class="keyword">else</span> <span class="keyword">return</span>,1

<span class="keyword">end</span> 



<span class="comment">;docstart::postgres::table_indexes</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::table_indexes</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  Print out index info for a table.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  if pg-&gt;table_indexes, tablename, struct=, connect_info=, status=</span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  tablename:  The name of the table.</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL INPUTS:</span>
<span class="comment">;  connect_info:  List of connection options separated by semicolon.</span>
<span class="comment">;        e.g. "user=somename;dbname=somename"</span>
<span class="comment">;   http://www.postgresql.org/docs/8.0/interactive/libpq.html#LIBPQ-CONNECT</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL OUTPUTS:</span>
<span class="comment">;  struct=: The structure for the index descriptions.</span>
<span class="comment">;  status: The status of the query.  See the ::status_val() method for</span>
<span class="comment">;    the meaning of this output.</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: 2006-05-19  Erin Sheldon NYU</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::table_indexes</span>

<span class="keyword">pro</span><span class="function-name"> postgres::table_indexes</span>, tablename, struct=struct, connect_info=connect_info, status=status

  tname = strlowcase( strtrim(string( tablename[0] ), 2) )
  <span class="keyword">if</span> <span class="keyword">not</span> self-&gt;table_exists(tablename) <span class="keyword">then</span> <span class="keyword">begin</span> 
      message,<span class="string">'No table called '</span>+tname,/inf
      <span class="keyword">return</span>
  <span class="keyword">endif</span> 

  <span class="comment">;; Get the oid for this table</span>
  query = $
    <span class="string">"SELECT "</span> +$
    <span class="string">"  c.oid, "</span>+$
    <span class="string">"  n.nspname, "</span>+$
    <span class="string">"  c.relname "</span>+$
    <span class="string">"FROM "</span>+$
    <span class="string">"  pg_catalog.pg_class c "</span>+$
    <span class="string">"LEFT JOIN "</span>+$
    <span class="string">"  pg_catalog.pg_namespace n ON n.oid = c.relnamespace "</span>+$
    <span class="string">"WHERE "</span>+$
    <span class="string">"  pg_catalog.pg_table_is_visible(c.oid) AND c.relname ~ '^"</span>+tname+<span class="string">"$' "</span>+$
    <span class="string">"ORDER BY 2, 3"</span>

  
  oid_struct = self-&gt;query(query, connect_info=connect_info, status=status)
  <span class="keyword">if</span> status <span class="keyword">ne</span> self-&gt;status_val(<span class="string">'success'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
<span class="comment">;      message,"could not retrieve oid for table '"+tname+"'",/inf</span>
      <span class="keyword">return</span>
  <span class="keyword">endif</span> 
  <span class="keyword">if</span> self-&gt;nrows() <span class="keyword">ne</span> 1 <span class="keyword">then</span> <span class="keyword">begin</span> 
      message,<span class="string">'More than one row returned.  That should not happen'</span>
  <span class="keyword">endif</span> 
  oidstr = ntostr( oid_struct.oid )


  <span class="comment">;; Now that we have the oid, we can get the index list</span>
  query = $
    <span class="string">"SELECT "</span>                                 +$
    <span class="string">"  c2.relname as index, "</span>                 +$
    <span class="string">"  i.indisprimary as primary, "</span>           +$
    <span class="string">"  i.indisunique as unique, "</span>             +$
    <span class="string">"  i.indisclustered as clustered, "</span>       +$
    <span class="string">"  pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as indexdef "</span>+$
    <span class="string">"FROM "</span>                                   +$
    <span class="string">"  pg_catalog.pg_class c, "</span>               +$
    <span class="string">"  pg_catalog.pg_class c2, "</span>              +$
    <span class="string">"  pg_catalog.pg_index i "</span>                +$
    <span class="string">"WHERE "</span>                                  +$
    <span class="string">"  c.oid = '"</span>+oidstr+<span class="string">"' AND "</span>             +$
    <span class="string">"  c.oid = i.indrelid AND "</span>               +$
    <span class="string">"  i.indexrelid = c2.oid "</span>                +$
    <span class="string">"ORDER BY "</span>+$
    <span class="string">"  i.indisprimary DESC, i.indisunique DESC, c2.relname"</span>

  struct = self-&gt;query(query, connect_info=connect_info, status=status)
  <span class="keyword">if</span> status <span class="keyword">ne</span> self-&gt;status_val(<span class="string">'success'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
<span class="comment">;      message,"could not retrieve indexes for table '"+tname+"'",/inf</span>
      <span class="keyword">return</span>
  <span class="keyword">endif</span> 

  <span class="comment">;; print the output</span>

  nindex = n_elements(struct)
  idesc = strarr(nindex)


  <span class="keyword">for</span> i=0l, nindex-1 <span class="keyword">do</span> <span class="keyword">begin</span> 
      def = struct[i].indexdef

      <span class="keyword">if</span> struct[i].primary <span class="keyword">then</span> <span class="keyword">begin</span> 
          idesc[i] = <span class="string">'PRIMARY KEY, '</span>
      <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">if</span> stregex(def, <span class="string">'UNIQUE'</span>, /bool) <span class="keyword">then</span> <span class="keyword">begin</span> 
          idesc[i] = <span class="string">'UNIQUE KEY, '</span>
      <span class="keyword">endif</span> 

      iuse = stregex(def, <span class="string">'USING'</span>)
      <span class="keyword">if</span> iuse <span class="keyword">ne</span> -1 <span class="keyword">then</span> <span class="keyword">begin</span> 
          idesc[i] = idesc[i] + strmid(def, iuse+6)
      <span class="keyword">endif</span> 

  <span class="keyword">endfor</span> 

  maxrel = max( strlen( struct.index ) ) 
  maxdef = max( strlen( idesc ) ) 

  
  padding = 3
  width = maxrel+maxdef+2*padding

  divider = mkstr(width+2, val=<span class="string">'-'</span>)

  relformat = <span class="string">'A'</span>+ntostr(maxrel+padding)
  defformat = <span class="string">'A'</span>+ntostr(maxdef+padding)
  format = <span class="string">'('</span>+relformat+<span class="string">','</span>+defformat+<span class="string">')'</span>
  
  print,<span class="string">"Indexes for '"</span>+tname+<span class="string">"'"</span>
  print
  print, <span class="string">'index'</span>, <span class="string">'description'</span>, format=format
  print,divider

  <span class="keyword">for</span> i=0l, nindex-1 <span class="keyword">do</span> <span class="keyword">begin</span> 
      print,$
        struct[i].index, idesc[i], $
        format=format
  <span class="keyword">endfor</span> 

<span class="keyword">end</span> 


<span class="comment">;docstart::postgres::describe</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::describe</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  Print a description either the tables in the database or, if a tablename</span>
<span class="comment">;  is sent, a description ofthe table layout. In that case it runs</span>
<span class="comment">;       select * from tablename limit 1</span>
<span class="comment">;  and then help, result, /struct on the result.  The structure can be</span>
<span class="comment">;  returned via the keyword.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  if pg-&gt;describe, [tablename, struct=, /all, connect_info=, status=]</span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  If no inputs, a short description of all public tables is displayed.</span>
<span class="comment">;  If /all, the other tables are also described.</span>
<span class="comment">;</span>
<span class="comment">;  tablename:  The name of the table.</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL INPUTS:</span>
<span class="comment">;  /all: Return a list of all tables, not just public.</span>
<span class="comment">;  connect_info:  List of connection options separated by semicolon.</span>
<span class="comment">;        e.g. "user=somename;dbname=somename"</span>
<span class="comment">;   http://www.postgresql.org/docs/8.0/interactive/libpq.html#LIBPQ-CONNECT</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL OUTPUTS:</span>
<span class="comment">;  struct=: The structure containing the description.</span>
<span class="comment">;  status: The status of the query.  See the ::status_val() method for</span>
<span class="comment">;    the meaning of this output.</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: 2006-05-19  Erin Sheldon NYU</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::describe_table</span>

<span class="keyword">pro</span><span class="function-name"> postgres::describe</span>, tablename, struct=struct, all=all, connect_info=connect_info, status=status


  <span class="keyword">if</span> n_elements(tablename) <span class="keyword">ne</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      tname = strtrim(string(tablename[0]), 2)
      query = <span class="string">"select * from "</span>+tname+<span class="string">" limit 1"</span>

      struct = self-&gt;query(query, connect_info=connect_info, status=status)
      <span class="keyword">if</span> status <span class="keyword">eq</span> self-&gt;status_val(<span class="string">'success'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
          help,struct,/str
          self-&gt;table_indexes, tname
      <span class="keyword">endif</span>  
  <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span> 
      struct = self-&gt;tables(all=all, /struct, status=status)
      <span class="keyword">if</span> status <span class="keyword">eq</span> self-&gt;status_val(<span class="string">'success'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
          print_struct, struct
      <span class="keyword">endif</span>  
  <span class="keyword">endelse</span> 

<span class="keyword">end</span> 




<span class="comment">;docstart::postgres::tablerows</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::tablerows()</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  If the metatable exists for the input tablename, return the number</span>
<span class="comment">;  of listed rows.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  nrows = pg-&gt;tablerows(tablename, connect_info=, status=st) </span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  tablename:  The name of the table.</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL INPUTS:</span>
<span class="comment">;  connect_info:  List of connection options separated by semicolon.</span>
<span class="comment">;        e.g. "user=somename;dbname=somename"</span>
<span class="comment">;   http://www.postgresql.org/docs/8.0/interactive/libpq.html#LIBPQ-CONNECT</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL OUTPUTS:</span>
<span class="comment">;  status: The status of the query.  See the ::status_val() method for</span>
<span class="comment">;    the meaning of this output.</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: 2006-05-19  Erin Sheldon NYU</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::tablerows</span>

<span class="keyword">function</span><span class="function-name"> postgres::tablerows</span>, tablename, connect_info=connect_info, status=status

  metatable = tablename + <span class="string">'_meta'</span>
  query = <span class="string">'select * from '</span>+metatable
  res = self-&gt;query(query, connect_info=connect_info, status=status)
  <span class="keyword">if</span> status <span class="keyword">ne</span> self-&gt;status_val(<span class="string">'success'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
      <span class="keyword">return</span>, -1
  <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span> 
      <span class="keyword">if</span> tag_exist(res, <span class="string">'nrows'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
          tablerows = res.nrows
          ntr = n_elements(tablerows) 
          <span class="keyword">return</span>, tablerows[ntr-1]
      <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span> 
          message,<span class="string">'NROWS does not exist in table '</span>+metatable,/inf
          <span class="keyword">return</span>, -1
      <span class="keyword">endelse</span> 
  <span class="keyword">endelse</span> 

<span class="keyword">end</span> 




<span class="comment">;docstart::postgres::postgres_type</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::postgres_type()</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  Method of the postgres class to convert an idl type description to a</span>
<span class="comment">;  postgres type description.  The idl type description is that returned</span>
<span class="comment">;  from the size(var, /tname) call.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  pgtype = pg-&gt;postgres_type(idltype)</span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  struct: An IDL type description.</span>
<span class="comment">;</span>
<span class="comment">; KEYWORD PARAMETERS:</span>
<span class="comment">;  length: For strings, use this as the lenght of a VARCHAR definition.</span>
<span class="comment">;    default is to use the more flexible, but less efficient, TEXT </span>
<span class="comment">;    format.</span>
<span class="comment">;</span>
<span class="comment">; OUTPUTS:</span>
<span class="comment">;  A string containing the postgres type name.</span>
<span class="comment">;</span>
<span class="comment">; RESTRICTIONS:</span>
<span class="comment">;  Currently COMPLEX, STRUCTURE, and POINTER are not supported.</span>
<span class="comment">;</span>
<span class="comment">; EXAMPLE:</span>
<span class="comment">;  IDL&gt; tn = size(var, /tname)</span>
<span class="comment">;  IDL&gt; pg = obj_new('posgres')</span>
<span class="comment">;  IDL&gt; pgtype = pg-&gt;postgres_type(tn)</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: Some time mid 2005, Erin Sheldon, UChicago</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::postgres_type</span>


<span class="keyword">function</span><span class="function-name"> postgres::postgres_type</span>, tname, length=length

  <span class="keyword">if</span> n_params() <span class="keyword">lt</span> 1 <span class="keyword">then</span> <span class="keyword">begin</span> 
      print,<span class="string">'-Syntax: pgtyep = obj-&gt;postgres_type(idl_type_name, length=)'</span>
      <span class="keyword">return</span>,<span class="string">''</span>
  <span class="keyword">endif</span> 

  <span class="comment">;; Because there are no unsigned types in postgres, we must</span>
  <span class="comment">;; convert uint to long, ulong to long64, etc</span>
  <span class="comment">;; There may be issues with the ULONG64 since it is not supported</span>
  <span class="comment">;; by postgres</span>
  <span class="keyword">case</span> strupcase(tname) <span class="keyword">of</span> 
      <span class="string">'BYTE'</span>:    <span class="keyword">return</span>,<span class="string">'SMALLINT'</span>
      <span class="string">'INT'</span>:     <span class="keyword">return</span>,<span class="string">'SMALLINT'</span>
      <span class="string">'UINT'</span>:    <span class="keyword">return</span>,<span class="string">'INT'</span>
      <span class="string">'LONG'</span>:    <span class="keyword">return</span>,<span class="string">'INT'</span>
      <span class="string">'ULONG'</span>:   <span class="keyword">return</span>,<span class="string">'BIGINT'</span>
      <span class="string">'LONG64'</span>:  <span class="keyword">return</span>,<span class="string">'BIGINT'</span>
      <span class="string">'ULONG64'</span>: <span class="keyword">return</span>,<span class="string">'BIGINT'</span>
      <span class="string">'FLOAT'</span>:   <span class="keyword">return</span>,<span class="string">'REAL'</span>
      <span class="string">'DOUBLE'</span>:  <span class="keyword">return</span>,<span class="string">'DOUBLE PRECISION'</span>
      <span class="string">'STRING'</span>: <span class="keyword">BEGIN</span> 
          <span class="keyword">if</span> n_elements(length) <span class="keyword">eq</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
              <span class="keyword">return</span>,<span class="string">'TEXT'</span>
          <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span>
              <span class="keyword">return</span>,<span class="string">'VARCHAR('</span>+strtrim( long(length[0]) ,2)+<span class="string">')'</span>
          <span class="keyword">endelse</span> 
      <span class="keyword">end</span> 
  <span class="keyword">endcase</span> 

<span class="keyword">end</span> 

<span class="comment">;docstart::postgres::struct2coldefs</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::struct2coldefs()</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  Method of the postgres class to convert a structure into a list SQL type</span>
<span class="comment">;  definitions for each tag.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  tdef=pg-&gt;struct2coldefs(struct, /varchar, tags=, /verbose)</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  struct: A structure.</span>
<span class="comment">;</span>
<span class="comment">; KEYWORD PARAMETERS:</span>
<span class="comment">;  /varchar: Use VARCHAR for strings rather than TEXT.  More efficient, but</span>
<span class="comment">;    if the max lenght is determined from the input structure so one must</span>
<span class="comment">;    be careful that the lenght is representative of all data to be put</span>
<span class="comment">;    in the table.</span>
<span class="comment">;  tags=: A list of tags from the structure. Default is to use all.</span>
<span class="comment">;  /verbose: Print out the tag definitions.</span>
<span class="comment">;</span>
<span class="comment">; OUTPUTS:</span>
<span class="comment">;  A string array containing the column definitions.</span>
<span class="comment">;</span>
<span class="comment">; EXAMPLE:</span>
<span class="comment">;  IDL&gt; struct = mrdfits('some_fits_file.fits', 1)</span>
<span class="comment">;  IDL&gt; pg = obj_new('posgres')</span>
<span class="comment">;  IDL&gt; cdef = pg-&gt;struct2coldefs(struct)</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: Some time mid 2005, Erin Sheldon, UChicago</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::struct2coldefs</span>

<span class="keyword">function</span><span class="function-name"> postgres::struct2coldefs</span>, struct, varchar=varchar, tags=tags, verbose=verbose

  <span class="keyword">if</span> n_params() <span class="keyword">lt</span> 1 <span class="keyword">then</span> <span class="keyword">begin</span> 
      print,<span class="string">'-Syntax: coldefs = obj-&gt;struct2coldefs(struct, /varchar, tags=, /verbose)'</span>
      <span class="keyword">return</span>,<span class="string">''</span>
  <span class="keyword">endif</span> 

  <span class="comment">;; generate entries in a pgsql create table statement from the input</span>
  <span class="comment">;; structure. </span>

  tags = strlowcase( tag_names(struct) )
  ntags = n_elements(tags)

  <span class="keyword">for</span> i=0l, ntags-1 <span class="keyword">do</span> <span class="keyword">begin</span> 

      tmpvar = struct[0].(i)
      nelem = n_elements(tmpvar)

      tn = size(tmpvar,/tname)

      <span class="keyword">if</span> tn <span class="keyword">eq</span> <span class="string">'STRING'</span> <span class="keyword">and</span> keyword_set(varchar) <span class="keyword">then</span> <span class="keyword">begin</span> 
          len = max( strlen( struct.(i) ) )
          pgsql_type = self-&gt;postgres_type(tn, length=len)
      <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span>          
          pgsql_type = self-&gt;postgres_type(tn)
      <span class="keyword">endelse</span> 

      <span class="keyword">if</span> nelem <span class="keyword">gt</span> 1 <span class="keyword">then</span> pgsql_type = pgsql_type + <span class="string">'['</span>+ntostr(nelem)+<span class="string">']'</span>
          
      coldef = tags[i] + <span class="string">' '</span>+pgsql_type
      <span class="keyword">if</span> tn <span class="keyword">ne</span> <span class="string">'STRING'</span> <span class="keyword">then</span> coldef = coldef + <span class="string">' NOT NULL'</span>

      <span class="keyword">if</span> keyword_set(verbose) <span class="keyword">then</span> <span class="keyword">begin</span> 
          print,tags[i],<span class="string">' '</span>+tn
          print,<span class="string">' |--&gt; '</span>+coldef
      <span class="keyword">endif</span> 

      add_arrval, coldef, coldefs

  <span class="keyword">endfor</span> 

  <span class="keyword">return</span>,coldefs

<span class="keyword">end</span> 


<span class="comment">;docstart::postgres::struct2tabledef</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::struct2tabledef()</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  Method of the postgres class to convert a structure into an SQL</span>
<span class="comment">;  CREATE TABLE statement.  Allows defining of the primary key.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  tdef=pg-&gt;struct2tabledef(struct, tablename, primary_key=, /varchar, file=)</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  struct: A structure.</span>
<span class="comment">;  tablename:  The name of the table into which the data will be stuffed.</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; KEYWORD PARAMETERS:</span>
<span class="comment">;  primary_key: The tag to be used as the primary key.</span>
<span class="comment">;  /varchar: Use VARCHAR for strings rather than TEXT.  More efficient, but</span>
<span class="comment">;    if the max lenght is determined from the input structure so one must</span>
<span class="comment">;    be careful that the lenght is representative of all data to be put</span>
<span class="comment">;    in the table.</span>
<span class="comment">;  file: A file to write the create table statement to.</span>
<span class="comment">;</span>
<span class="comment">; OUTPUTS:</span>
<span class="comment">;  A string containing the CREATE TABLE statement.</span>
<span class="comment">;</span>
<span class="comment">; EXAMPLE:</span>
<span class="comment">;  IDL&gt; struct = mrdfits('some_fits_file.fits', 1)</span>
<span class="comment">;  IDL&gt; pg = obj_new('posgres')</span>
<span class="comment">;  IDL&gt; tdef = pg-&gt;struct2tabledef(struct, 'newtable', primary_key='index')</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: Some time mid 2005, Erin Sheldon, UChicago</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::struct2tabledef</span>

<span class="keyword">function</span><span class="function-name"> postgres::struct2tabledef</span>, struct, tablename, varchar=varchar, file=file, primary_key=primary_key

  on_error, 2
  <span class="keyword">if</span> n_elements(struct) <span class="keyword">eq</span> 0 <span class="keyword">or</span> n_elements(tablename) <span class="keyword">eq</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      print,<span class="string">'-Syntax: pg-&gt;struct2tabledef, struct, tablename, /varchar, file=, primary_key='</span>
      print
      message,<span class="string">'Halting'</span>
  <span class="keyword">endif</span> 

  <span class="keyword">if</span> n_elements(file) <span class="keyword">eq</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      lun = -1
  <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span> 
      openw,lun,file,/get_lun
  <span class="keyword">endelse</span> 

  coldefs = self-&gt;struct2coldefs(struct, varchar=varchar)

  tags = tag_names(struct)
  

  <span class="comment">;; add the primary key</span>
  <span class="keyword">if</span> n_elements(primary_key) <span class="keyword">eq</span> 1 <span class="keyword">and</span> $
    size(primary_key, /tname) <span class="keyword">EQ</span> <span class="string">'STRING'</span> <span class="keyword">then</span> <span class="keyword">begin</span> 
      
      <span class="keyword">if</span> tag_exist(struct, primary_key) <span class="keyword">then</span> <span class="keyword">begin</span> 
          coldefs = [coldefs, <span class="string">'PRIMARY KEY ('</span>+primary_key+<span class="string">')'</span>]
      <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span>    
          message,<span class="string">'tag '</span>+primary_key+<span class="string">' does not exist'</span>,/inf
          print,tag_names(struct)
      <span class="keyword">endelse</span> 
  <span class="keyword">endif</span> 

  tabledef = $
    <span class="string">'CREATE TABLE '</span>+tablename + <span class="string">' '</span>+$
    <span class="string">'('</span>+strjoin(coldefs,<span class="string">', '</span>)+<span class="string">')'</span>
  

  ncoldefs = n_elements(coldefs)
  printf,lun,<span class="string">'CREATE TABLE '</span>+tablename
  printf,lun,<span class="string">'('</span>
  <span class="keyword">for</span> i=0l, ncoldefs-2 <span class="keyword">do</span> <span class="keyword">begin</span> 
      printf,lun,coldefs[i]+<span class="string">', '</span>
  <span class="keyword">endfor</span> 
  printf,lun,coldefs[i]
  printf,lun,<span class="string">');'</span>

  <span class="keyword">if</span> n_elements(file) <span class="keyword">ne</span> 0 <span class="keyword">then</span> free_lun,lun

  <span class="keyword">return</span>, tabledef


<span class="keyword">end</span> 


<span class="comment">;docstart::postgres::struct2table</span>
<span class="comment">;</span>
<span class="comment">; NAME:</span>
<span class="comment">;  postgres::struct2table  </span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; PURPOSE:</span>
<span class="comment">;  Method of the postgres class to write a structure into a postgres</span>
<span class="comment">;  database.</span>
<span class="comment">;</span>
<span class="comment">; CALLING SEQUENCE:</span>
<span class="comment">;  pg-&gt;struct2table, struct, tablename, primary_key=, connect_info=,</span>
<span class="comment">;                    tmpdir=, status=</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; INPUTS:</span>
<span class="comment">;  struct: A structure.  May be an array.</span>
<span class="comment">;  tablename:  The name of the table into which the data will be stuffed.</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; KEYWORD PARAMETERS:</span>
<span class="comment">;  primary_key: The tag to be used as the primary key.  Only used if the</span>
<span class="comment">;       table is created.</span>
<span class="comment">;  connect_info: Infor used for connecting. This is the standard string</span>
<span class="comment">;       sent to postgres clients: e.g. </span>
<span class="comment">;                  "user=username;password=pass;host=hostname;"</span>
<span class="comment">;       If not sent, this info is gotten from the environment variables.</span>
<span class="comment">;       and ~/.pgpass file.</span>
<span class="comment">;  tmpdir: Directory to write the temporary postgres input file.</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; OPTIONAL OUTPUTS:</span>
<span class="comment">;  status: The status of the query.  See the ::status_val() method for</span>
<span class="comment">;    the meaning of this output.</span>
<span class="comment">;</span>
<span class="comment">; SIDE EFFECTS:</span>
<span class="comment">;  Data is stuffed into the table. The table if non-existent will be</span>
<span class="comment">;  created if the permissions are sufficient.</span>
<span class="comment">;</span>
<span class="comment">;</span>
<span class="comment">; RESTRICTIONS:</span>
<span class="comment">;  The user must have the postgres password info in their ~/.pgpass file.</span>
<span class="comment">;  This is becuase the COPY command may only be run as the postgres</span>
<span class="comment">;  user.</span>
<span class="comment">;</span>
<span class="comment">; EXAMPLE:</span>
<span class="comment">;  IDL&gt; struct = mrdfits('some_fits_file.fits', 1)</span>
<span class="comment">;  IDL&gt; pg = obj_new('posgres')</span>
<span class="comment">;  IDL&gt; pg-&gt;struct2table, struct, 'newtable'</span>
<span class="comment">;</span>
<span class="comment">; MODIFICATION HISTORY:</span>
<span class="comment">;  Created: Some time mid 2005, Erin Sheldon, UChicago</span>
<span class="comment">;</span>
<span class="comment">;docend::postgres::struct2table</span>

<span class="keyword">pro</span><span class="function-name"> postgres::struct2table</span>, struct, tablename, primary_key=primary_key, varchar=varchar, status=status, tmpdir=tmpdir, connect_info=connect_info, createonly=createonly

  status = 1
  on_error, 2
  <span class="keyword">if</span> n_elements(struct) <span class="keyword">eq</span> 0 <span class="keyword">or</span> n_elements(tablename) <span class="keyword">eq</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      print,<span class="string">'-Syntax: pg-&gt;struct2table, struct, tablename, primary_key=, '</span>+$
        <span class="string">'/varchar, tmpdir=, connect_info=, createonly=creatonly, status='</span>
      print
      message,<span class="string">'Halting'</span>
  <span class="keyword">endif</span> 

  <span class="comment">;; if the table already exists, no need to create a table </span>
  <span class="comment">;; definition.</span>

  texist = self-&gt;table_exists(tablename)
  <span class="keyword">if</span> texist <span class="keyword">and</span> keyword_set(createonly) <span class="keyword">then</span> <span class="keyword">begin</span> 
      message,<span class="string">'/createonly set but table already exists'</span>,/inf
      message,<span class="string">'Nothing done'</span>,/inf
      status = 0
      <span class="keyword">return</span>
  <span class="keyword">endif</span> 

  <span class="keyword">if</span> <span class="keyword">not</span> texist <span class="keyword">then</span> <span class="keyword">begin</span> 

      message,<span class="string">'Creating table definition'</span>,/inf
      <span class="comment">;; WE will generate the table</span>
      tabledef = self-&gt;struct2tabledef(struct, tablename, $
                                       primary_key=primary_key, $
                                       varchar=varchar)

      <span class="comment">;; Create the table</span>
      message,<span class="string">'Creating table'</span>,/inf
      self-&gt;query, tabledef, status=qstatus, connect_info=connect_info
      
      <span class="keyword">if</span> qstatus <span class="keyword">ne</span> self-&gt;status_val(<span class="string">'no_result'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
          message,<span class="string">'Could not create table'</span>,/inf
          <span class="keyword">return</span>
      <span class="keyword">endif</span> 

      <span class="keyword">if</span> keyword_set(createonly) <span class="keyword">then</span> <span class="keyword">begin</span> 
          status=0
          <span class="keyword">return</span>
      <span class="keyword">endif</span> 

  <span class="keyword">endif</span> 

  <span class="comment">;; now write the input file.  ascii for now</span>
  <span class="keyword">if</span> n_elements(tmpdir) <span class="keyword">eq</span> 0 <span class="keyword">then</span> tmpdir = <span class="string">'~'</span>
  tmpdir = expand_tilde(tmpdir)

  file = tmpfile(prefix=tablename+<span class="string">'-stuff-'</span>)+<span class="string">'.pgsql'</span>
  file = concat_dir(tmpdir, file)
  
  message,<span class="string">'Writing input file: '</span>+file,/inf
  self-&gt;input_write, struct, file, status=wstatus
  <span class="keyword">if</span> wstatus <span class="keyword">ne</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span> 
      message,<span class="string">'Could not write postgres input file'</span>
      <span class="keyword">return</span>
  <span class="keyword">endif</span> 

  <span class="keyword">if</span> <span class="keyword">not</span> fexist(file) <span class="keyword">then</span> <span class="keyword">begin</span> 
      message,<span class="string">'Cannot find the file in /tmp'</span>
  <span class="keyword">endif</span> 

  <span class="comment">;; Stuff the table.  Must do this as postgres; this is</span>
  <span class="comment">;; a security hole if no password protection is set up</span>
  <span class="comment">;; for the postgres account.</span>

  query = <span class="string">"COPY "</span>+tablename+<span class="string">" FROM '"</span>+file+<span class="string">"'"</span>
  message,query,/inf
  self-&gt;query, query, connect_info=<span class="string">'user=postgres'</span>, status = stuff_status
  <span class="keyword">if</span> stuff_status <span class="keyword">ne</span> self-&gt;status_val(<span class="string">'no_result'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
      message,<span class="string">'Failed to stuff file'</span>,/inf
  <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span> 
      file_delete, file
      status = 0
  <span class="keyword">endelse</span> 



  <span class="keyword">return</span>

<span class="keyword">end</span> 





<span class="comment">; does not support multi-column indexes</span>
<span class="keyword">pro</span><span class="function-name"> postgres::create_index</span>, table, columns, connect_info=connect_info

    <span class="keyword">if</span> n_params() <span class="keyword">lt</span> 2 <span class="keyword">then</span> <span class="keyword">begin</span> 
        on_error, 2
        print,<span class="string">'-Syntax: pg-&gt;create_index, table, columns, connect_info='</span>
        print
        message,<span class="string">'Halting'</span>
    <span class="keyword">endif</span> 

    <span class="comment">;; only trick here is dealing with arrays</span>
    ncols = n_elements(columns)
    cstr = strarr(ncols)
    inames = strarr(ncols)
    <span class="keyword">for</span> i=0l, ncols-1 <span class="keyword">do</span> <span class="keyword">begin</span> 

        <span class="keyword">if</span> strmatch(columns[i], <span class="string">'*\[*'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 

            <span class="comment">;; remove the braces for index name</span>
            tiname = repstr(columns[i], <span class="string">'['</span>, <span class="string">'_'</span>)
            tiname = repstr(tiname, <span class="string">']'</span>, <span class="string">''</span>)

            inames[i] = tiname
            cstr[i] = <span class="string">'(('</span>+columns[i]+<span class="string">'))'</span>
        <span class="keyword">endif</span> <span class="keyword">else</span> <span class="keyword">begin</span> 
            inames[i] = columns[i]
            cstr[i] = <span class="string">'('</span>+columns[i]+<span class="string">')'</span>
        <span class="keyword">endelse</span> 

    <span class="keyword">endfor</span> 

    create_index_arr = $
        <span class="string">'CREATE INDEX '</span>+table+<span class="string">'_'</span>+inames+<span class="string">'_index ON '</span>+table+<span class="string">' '</span>+cstr

    <span class="keyword">for</span> i=0l, ncols-1 <span class="keyword">do</span> <span class="keyword">begin</span> 
        query = create_index_arr[i]
        print,query
        self-&gt;query, query, connect_info=connect_info, status=status
        <span class="keyword">if</span> status <span class="keyword">ne</span> self-&gt;postgres::status_val(<span class="string">'no_result'</span>) <span class="keyword">then</span> <span class="keyword">begin</span> 
            message,<span class="string">'Error creating index'</span>
        <span class="keyword">endif</span> 
    <span class="keyword">endfor</span> 


<span class="keyword">end</span> 


<span class="keyword">pro</span><span class="function-name"> postgres::create_metatable</span>, table, connect_info=connect_info
    <span class="keyword">if</span> n_elements(table) <span class="keyword">eq</span> 0 <span class="keyword">then</span> <span class="keyword">begin</span>
        print,<span class="string">'-Syntax: p-&gt;create_metatable, tablename, connect_info='</span>
        on_error, 2
        message,<span class="string">'Halting'</span>
    <span class="keyword">endif</span>

    metatable = table+<span class="string">'_meta'</span>
    print
    print,<span class="string">'Creating metatable '</span>+metatable+<span class="string">' with nrows'</span>
 
    query = <span class="string">"CREATE TABLE "</span>+metatable+<span class="string">" (nrows BIGINT, modified TIMESTAMP NOT NULL DEFAULT('now'::text)::timestamp(6))"</span>
    self-&gt;query, query, conn=connect_info, status=status
    <span class="keyword">if</span> status <span class="keyword">ne</span> self-&gt;status_val(<span class="string">'no_result'</span>) <span class="keyword">then</span> message,<span class="string">'creation of metatable failed'</span>

    query = <span class="string">'INSERT INTO '</span>+metatable+<span class="string">' (nrows) SELECT count(*) AS nrows FROM '</span>+table
    print
    print,query
    self-&gt;query, query, conn=connect_info, status=status
    <span class="keyword">if</span> status <span class="keyword">ne</span> self-&gt;status_val(<span class="string">'no_result'</span>) <span class="keyword">then</span> message,<span class="string">'insert of nrows into metatable failed'</span>
<span class="keyword">end</span>



<span class="comment">;; write a structure as a postgres input file</span>
<span class="keyword">pro</span><span class="function-name"> postgres::input_write</span>, struct, file, status=status

  status = 1
  <span class="keyword">if</span> n_params() <span class="keyword">lt</span> 2 <span class="keyword">then</span> <span class="keyword">begin</span> 
      print,<span class="string">'-Syntax: pg-&gt;input_write, struct, file, status='</span>
      <span class="keyword">return</span>
  <span class="keyword">endif</span> 
  ascii_write, struct, file, /bracket_arrays, status=status
<span class="keyword">end</span> 


<span class="comment">;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;</span>
<span class="comment">;; set status to unknown, nrows to zero</span>
<span class="comment">;; the connect information is left untouched</span>
<span class="comment">;  i never use this.</span>
<span class="comment">;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;</span>

<span class="keyword">pro</span><span class="function-name"> postgres::reset</span>

  <span class="comment">;; don't change the connect info: </span>
  self.query_status = -1
  self.nrows = 0

<span class="keyword">end</span> 

<span class="keyword">function</span><span class="function-name"> postgres::cleanup</span>
  <span class="keyword">return</span>,1
<span class="keyword">end</span> 



<span class="keyword">pro</span><span class="function-name"> postgres__define</span>

  struct = {$
             postgres, $
             connect_info: <span class="string">''</span>, $
             query_status: -1, $
             nrows: 0ull $
            }

<span class="keyword">end</span> 

</pre>
<hr size=1 noshade>
This file was generated from IDL code by pro2html.pro in the SDSSIDL codebase<br>
<img src="./../images/email-14pt.png">
</BODY>
</HTML>
